# coding=utf-8

"""
copyright (c) 2009, paketka@gmail.com et. al
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

	* Redistributions of source code must retain the above copyright notice,
	  this list of conditions and the following disclaimer.

	* Redistributions in binary form must reproduce the above copyright notice,
	  this list of conditions and the following disclaimer in the documentation
	  and/or other materials provided with the distribution.

	* Neither the name of the <ORGANIZATION> nor the names of its contributors
	  may be used to endorse or promote products derived from this software
 	  without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
"""

import datetime
import re

from django.shortcuts import get_object_or_404, get_list_or_404
from django.db import models
from django.db.models import Q
from django.http import Http404
from django.utils.translation import ugettext as _
from django.db.models.signals import pre_save
from django.contrib.localflavor.us.models import PhoneNumberField
from django.core.exceptions import ObjectDoesNotExist, MultipleObjectsReturned
from django.core.exceptions import FieldError
from django.db import connection
from common.models import AutoSlug, makeSlug
import settings
from django.core.urlresolvers import reverse

from utils.charts import *

def term_to_id(term):
	if type(term) == type(0):
		term = get_object_or_404(TermOfOffice, id = term)
		return term.id
	else:
		return term.id


def person_to_id(person):
	if type(person) == type(0):
		return person
	else:
		return person.id


def group_to_id(group):
	if type(group) == type(0):
		return group
	else:
		return group.id


def poll_to_id(poll):
	if type(poll) == type(0):
		return group
	else:
		return poll.id

# Create your models here.
class TermOfOffice(AutoSlug):
	yearStart = models.CharField(_("Start Year"), max_length = 4)
	yearEnd = models.CharField(_("End Year"), max_length = 4)
	slug = models.SlugField(db_index = True, unique = True)
	termId = models.CharField(_("Term Id"), max_length = 4)
	slugs = {
		'slug' : ('yearStart', 'yearEnd', ),
	}
							
	class Meta:
		verbose_name = _("Term of Office")
		verbose_name_plural = _("Terms of Office")
	
	class Admin:
		pass


	def isComplete(self):
		return self.yearEnd != ''

	def getStart(self):
		return int(self.yearStart)

	def getEnd(self):
		if self.yearEnd == '':
			return datetime.datetime.today().year
		else:
			return int(self.yearEnd)

	def getRange(self):
		retVal = (
			datetime.date(	day = 1,
							month = 1,
							year = self.getStart()
						),
			datetime.date(	day = 31,
							month = 12,
							year = self.getEnd()
						),
			)
			
		return retVal

	def getStartDate(self):
		retVal = datetime.date(
						day = 1,
						month = 1,
						year = self.getStart()
					)
		return retVal
	
	def getEndDate(self):
		retVal = datetime.date(
						day = 31,
						month = 12,
						year = self.getEnd()
					)
		return retVal

	def getTermsList(self):
		"""
		similar to to get_term_list. the instance method is used when
		term is known - the instance is currentTerm
		"""
		retVal = []
		formatStr = u'<a href="%s">' + _(u'Všechna období') + '</a>'
		url = formatStr % reverse('index_html')
		retVal.append(url)

		for t in TermOfOffice.objects.all():
			url = u''
			content = '%d - %d' % (t.getStart(), t.getEnd())
			if t == self:
				url = u'<span>%s</span>' % content
				url += u'<span class="ui-icon ui-icon-triangle-1-w"></span>'
			else:
				url = u'<a href="%s">%s</a>' % (
							reverse('index_term_html',
										args = [ 
											str(t.getStart()),
											str(t.getEnd())
										]
							),
							content,
						)
			retVal.append(url)

		return retVal

	@staticmethod
	def get_term_or_404(start, end):
		"""
		returns term for desired range defined by start year and end year
		if no such term is found Http404 exception is raised

		the start, end parameters must match the term boundary. the current
		term is exception to that rule, only the start must match yearStart
		"""
		retVal = None

		if type(start) != type(''):
			start = str(start)

		if type(end) != type(''):
			end = str(end)

		try:
			retVal = TermOfOffice.objects.get(yearStart = start, yearEnd = end)
		except ObjectDoesNotExist:
			if (int(start) < int(end)):
				try:
					retVal = TermOfOffice.objects.get(
									yearStart = start,
									yearEnd = ''
								)
				except ObjectDoesNotExist:
					raise Http404
			else:
				raise Http404

		return retVal

	@staticmethod
	def get_terms_list():
		"""
		returns the list of URLs for other terms
		"""
		retVal = []
		url = u'<span>%s</span>' % _(u'Všechna období')
		url += u'<span class="ui-icon ui-icon-triangle-1-w"></span>'
		retVal.append(url)

		for t in TermOfOffice.objects.all():
			url = u''
			content = '%d - %d' % (t.getStart(), t.getEnd())
			url = u'<a href="%s">%s</a>' % (
						reverse('index_term_html',
									args = [ 
										str(t.getStart()),
										str(t.getEnd())
									]
						),
						content,
					)
			retVal.append(url)

		return retVal

	@staticmethod
	def get_term_for_date(date):
		for t in TermOfOffice.objects.all():
			if date >= t.getStartDate() and date <= t.getEndDate():
				return t

		return None
				

pre_save.connect(makeSlug, TermOfOffice)


class Division(AutoSlug):
	term = models.ForeignKey(TermOfOffice)
	name = models.CharField(_("Division Name"), max_length = 20)
	icon = models.ImageField(_("Division Symbol"), 
								upload_to = settings.SYMBOLS,
								blank = True
							)
	divId = models.CharField(_("Division Id"), max_length = 10)
	slug = models.SlugField(db_index = True, unique = True)
	slugs = {
		'slug' : ('name', )
	}

	class Meta:
		verbose_name = _("Division")
		verbose_name_plural = _("Divisions")
		unique_together = (('term', 'divId',),)

	def getName(self):
		pattern = re.compile(u'.*Volební\s*kraj(?P<name>.*)', re.U)
		match = pattern.match(self.name)
		retVal = u''
		if match:
			retVal += match.group('name')
		else:
			retVal = self.name
		
		return retVal.strip()
		

pre_save.connect(makeSlug, Division)


class PersonManager(models.Manager):
	def createPersonFromRow(self, row):
		"""
		returns person instance from row returned by SELECTs
		""" 
		person = self.model(
					id = row[0],	#primary Key
					mpId = row[1], #mpId
					name = row[2], #name
					surname = row[3], #surname
					title = row[4], #title
					titleLast = row[5], #titleLast
					birthDate = row[6], #birthDate
					email = row[7], #email
					office = row[8], #office
					regionOffice = row[9], #regionOffice
					phone = row[10], #phone number
					slug = row[11] # slug field
				)
					
		return person


	def commonSelect(self, where, orderBy, desc, term = None):
		"""
		Returns list of PersonObjects. There are some attributes added into
		each object in the list:
			detail,
			divisionName,
			partyName,
			age,
			absences,
			terms
		There are three manadatory arguments:
			where - is where clause to limit person select set
			orderBy - order by cluse
			desc - true to order object in descendant oreder
		the optional argument term limits person objects to desired term, if no
		term is passed list will contain data aggregation for all terms.
		"""
		retVal = []
		select = ''
		termId = None

		if orderBy != None and orderBy != '':
			if desc:
				orderBy += ' DESC'
			else:
				orderBy += ' ASC'

		if term != None:
			termId = term_to_id(term)
			select = u"""
				SELECT DISTINCT person.id,
					person.mpId,
					person.name,
					person.surname,
					person.title,
					person.titleLast,
					person.birthDate,
					person.email,
					person.office,
					person.regionOffice,
					person.phone,
					person.slug,
					detail.id,
					division.id,
					party.id,
					SUM(votes.absences) + SUM(votes.excused) AS absences,
					SUM(votes.absences) + SUM(votes.excused) +
							SUM(votes.votes_refrains) +
							SUM(votes.votes_aye) +
							SUM(votes.votes_nay) AS total,
					SUM(votes.votes_aye) AS votesAye,
					SUM(votes.votes_nay) AS votesNay,
					SUM(votes.votes_refrains) AS refrains,
					SUM(votes.absences) AS unexcAbs,
					SUM(votes.excused) AS excsdAbs
					FROM psp_person AS person
					INNER JOIN psp_group AS parlament ON parlament.type LIKE 'PARLAMENT' AND
								parlament.term_id = %d
					INNER JOIN psp_group AS party ON party.type LIKE 'KLUB' AND
								party.term_id = %d
					INNER JOIN psp_membership AS partymship ON
								partymship.group_id = party.id AND
								partymship.post LIKE '_len' AND
								partymship.person_id = person.id
					INNER JOIN psp_membership AS parlmship ON
								parlmship.group_id = parlament.id AND
								parlmship.post LIKE 'poslan%%%%' AND
								parlmship.person_id = person.id
					INNER JOIN psp_persondetail AS detail ON
								detail.person_id = person.id AND
								detail.term_id = %d
					INNER JOIN psp_division AS division ON
								division.id = detail.region_id
					INNER JOIN psp_votestats AS votes ON
								votes.person_id = person.id AND
								votes.term_id = %d
					%s
					GROUP BY person.id %s;
			"""
			select = select % (termId, termId, termId, termId, where, orderBy)
		else:
			select = u"""
				SELECT person.id,
					person.mpId,
					person.name,
					person.surname,
					person.title,
					person.titleLast,
					person.birthDate,
					person.email,
					person.office,
					person.regionOffice,
					person.phone,
					person.slug,
					detail.id,
					division.id,
					party.id,
					SUM(votes.absences) + SUM(votes.excused) AS absences,
					SUM(votes.absences) + SUM(votes.excused) +
							SUM(votes.votes_refrains) +
							SUM(votes.votes_aye) +
							SUM(votes.votes_nay) AS total,
					SUM(votes.votes_aye) AS votesAye,
					SUM(votes.votes_nay) AS votesNay,
					SUM(votes.votes_refrains) AS refrains,
					SUM(votes.absences) AS unexcAbs,
					SUM(votes.excused) AS excsdAbs
					FROM psp_person AS person
					INNER JOIN psp_group AS party ON party.type LIKE 'KLUB'
					INNER JOIN psp_membership AS partymship ON
								partymship.group_id = party.id AND
								partymship.post LIKE '_len' AND
								partymship.person_id = person.id
					INNER JOIN psp_persondetail AS detail ON
								detail.person_id = person.id
					INNER JOIN psp_division AS division ON
								division.id = detail.region_id
					INNER JOIN psp_votestats AS votes ON
								votes.person_id = person.id
					%s
					GROUP BY person.id %s;
				"""
			select = select % (where, orderBy)

		cursor = connection.cursor()
		cursor.execute(select)
		for row in cursor.fetchall():
			mp = self.createPersonFromRow(row)

			# row[14] - missed polls
			# row[15] - total number of all polls
			mp.percAbsences = get_percents(row[15], row[16])
			mp.absences = row[15]
			mp.votesAye = row[17]
			mp.votesNay = row[18]
			mp.refrains = row[19]
			mp.unexcAbs = row[20]
			mp.excsdAbs = row[21]

			mp.detail = None
			try:
				mp.detail = PersonDetail.objects.get(id = row[12])
			except ObjectDoesNotExist:
				pass

			mp.divisionName = ''
			try:
				# row[12] division ID
				mp.divisionName = Division.objects.get(id = row[13]).getName()
			except ObjectDoesNotExist:
				pass

			mp.partyName = ''
			try:
				# row[13] party club id
				mp.partyGroup = Group.objects.get(id = row[14])
				mp.partyName = mp.partyGroup.getPartyName() 
			except ObjectDoesNotExist:
				pass

			mp.terms = []
			for mship in Membership.objects.filter(
										person = mp,
										group__type = 'PARLAMENT'
									).order_by('group__term__id'):
				mp.terms.append(mship.group.term)
			
			mp.term = term

			if term != None:
				mp.terms.append(term)
				mp.age =  term.getStart() - mp.birthDate.year
			else:
				try:
					t = mp.terms[len(mp.terms) - 1]
					mp.age = t.getStart() - mp.birthDate.year
				except:
					mp.age = 0

			retVal.append(mp)

		return retVal


	def getPersons(self, desc = False, term = None):
		"""
		Uses commonSelect to retrieve list of peron objects.
		"""
		orderBy = 'ORDER BY person.surname, person.name'
		return self.commonSelect(	where = '',
									orderBy = orderBy,
									desc = desc,
									term = term
								)


	def getPersonsOrderByName(self, desc = False, term = None):
		"""
		Uses commonSelect to retreive list of person objects. Objects are
		ordered by name.
		
		"""
		orderBy = 'ORDER BY person.name, person.surname'
		return self.commonSelect(	where = '',
									orderBy = orderBy,
									desc = desc,
									term = term
								)


	def getPersonsOrderByAge(self, desc = False, term = None):
		"""
		Uses commonSelect to retreive list of person objects. Objects are
		ordered by age.
		"""
		orderBy = 'ORDER BY person.birthDate, person.surname, person.name'
		retVal = self.commonSelect(	where = '',
									orderBy = orderBy,
									desc = desc,
									term = term
								)
		#We need to sort by age here, age is computed with respect to the
		#start of parliementar membership.
		if desc:
			retVal.sort(lambda a, b: a.age - b.age)
		else:
			retVal.sort(lambda a, b: b.age - a.age)
			
		return retVal


	def getPersonsOrderByDivision(self, desc = False, term = None):
		"""
		Uses commonSelect to retreive list of person objects. Objects are
		ordered by division name.
		"""
		orderBy = 'ORDER BY division.name, person.surname, person.name'
		return self.commonSelect(	where = '',
									orderBy = orderBy,
									desc = desc,
									term = term
								)


	def getPersonsOrderByParty(self, desc = False, term = None):
		"""
		Uses commonSelect to retreive list of person objects. Objects are
		ordered by party name.
		"""
		orderBy = 'ORDER BY party.name, person.surname, person.name'
		return self.commonSelect(	where = '',
									orderBy = orderBy,
									desc = desc,
									term = term
								)

	
	def getPersonsOrderByAbsences(self, desc = False, term = None):
		"""
		Uses commonSelect to retreive list of person objects. Objects are
		ordered by party absences.
		"""
		orderBy = 'ORDER BY absences'
		retVal = self.commonSelect(	where = '',
									orderBy = '',
									desc = desc,
									term = term
								)
		#we need to sort by computed absences, which are in relative values,
		#the DB select sorts by absolute values, which are misleading
		#we need to keep 2 decimal places presition, therefore we mult by 100
		if desc:
			retVal.sort(
				lambda a, b: int(a.percAbsences * 100) - int(b.percAbsences * 100)
			)
		else:
			retVal.sort(
				lambda a, b: int(b.percAbsences * 100) - int(a.percAbsences * 100)
			)
		
		return retVal


	def getPerson(self, person, term = None):
		"""
		Retrieves single person object extended by attributes from common
		select. person is either person ID or person object to be retrieved.

		this should be changed to person instance method. but we would not
		be able to use commonSelect then.
		"""
		where = None
		if type(person) == type(0):
			where = 'WHERE person.id = %d' % person
		elif type(person) == type(u''):
			where = 'WHERE person.slug = "%s"' % person
		else:
			where = 'WHERE person.id = %d' % person.id

		list = self.commonSelect(	where = where,
									orderBy = '',
									desc = False,
									term = term
								)

		if len(list) != 1:
			raise ObjectDoesNotExist

		return list[0]
			

	def getAccordanceWithGroup(self, group, dayStart, dayEnd = None, asc = True):
		"""
		Retrieves list of persons object ordered by accordance with desired group.
		for particular person with particular. Each  Person object is extended
		by a new 'accordance' attribute.  Accordance attribute tells the percent of
		polls polled in accordance with group.
			group		- group to compute accordance with
			dayStart 	- date object defining start day
			dayEnd		- date object defining end day, it is optional
						  parameter, if no dayEnd is specified, then accordance
						  for desired dayStart date is retreived only.
			asc			- default true - how to order results (default from
						  least loyal to the most loyal)
		"""
		groupId = group_to_id(group)
		select = None
		totalPolls = 0

		if dayEnd != None:
			select = """		
				SELECT	personId,
						personMpId,
						personName,
						personSurname,
						personTitle,
						personTitleLast,
						personBirthDate,
						personEmail,
						personOffice,
						personRegionOffice,
						personPhone,
						personSlug,
						different,
						same FROM (
					SELECT	person.id AS personId,
							person.mpId AS personMpId,
							person.name AS personName,
							person.surname AS personSurname,
							person.title AS personTitle,
							person.titleLast AS personTitleLast,
							person.birthDate AS personBirthDate,
							person.email AS personEmail,
							person.office AS personOffice,
							person.regionOffice AS personRegionOffice,
							person.phone AS personPhone,
							person.slug AS personSlug,
							SUM(
								CASE
									WHEN AND gs.result <> vote.result THEN 1
									ELSE 0
								END
							) AS different,
							SUM(
								CASE
									WHEN AND gs.result == vote.result THEN 1
									ELSE 0
								END
							) AS same
							FROM psp_poll AS poll
								INNER JOIN psp_mpvote AS vote ON
											poll.id = vote.poll_id
								INNER JOIN psp_person AS person ON
											vote.person_id = person.id
								INNER JOIN psp_membership AS membership ON
											membership.person_id = person.id
								INNER JOIN psp_groupstats AS gs ON
											poll.id = gs.poll_id
							WHERE poll.date membership.group_id = %d AND
									BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d' AND
									gs.group_id = %d AND gs.result <> vote.result
							GROUP BY id
							ORDER BY count
			"""
			select = select % (	dayStart.year, dayStart.month, dayStart.day,
								dayEnd.year, dayEnd.month, dayEnd.day,
								groupId,
								groupId
							)
		else:
			select = """
				SELECT	personId,
						personMpId,
						personName,
						personSurname,
						personTitle,
						personTitleLast,
						personBirthDate,
						personEmail,
						personOffice,
						personRegionOffice,
						personPhone,
						personSlug,
						different,
						same FROM (
					SELECT	person.id AS personId,
							person.mpId AS personMpId,
							person.name AS personName,
							person.surname AS personSurname,
							person.title AS personTitle,
							person.titleLast AS personTitleLast,
							person.birthDate AS personBirthDate,
							person.email AS personEmail,
							person.office AS personOffice,
							person.regionOffice AS personRegionOffice,
							person.phone AS personPhone,
							person.slug AS personSlug,
							SUM(
								CASE
									WHEN AND gs.result <> vote.result THEN 1
									ELSE 0
								END
							) AS different,
							SUM(
								CASE
									WHEN AND gs.result == vote.result THEN 1
									ELSE 0
								END
							) AS same
							FROM psp_poll AS poll
								INNER JOIN psp_mpvote AS vote ON
											poll.id = vote.poll_id
								INNER JOIN psp_person AS person ON
											vote.person_id = person.id
								INNER JOIN psp_membership AS membership ON
											membership.person_id = person.id
								INNER JOIN psp_groupstats AS gs ON
											poll.id = gs.poll_id
							WHERE poll.date = '%d-%02d-%02d' AND
									membership.group_id = %d AND gs.group_id = %d
							GROUP BY id
							ORDER BY count"""
			select = select % (	dayStart.year, dayStart.month, dayStart.day,
								groupId,
								groupId
							)

		if asc:
			select += " ASC\n);"
		else:
			select += " DESC\n);"

		cursor = connection.cursor()
		cursor.execute(select)
		for row in cursor.fetchall():
			mp = self.createPersonFromRow(row)
			mp.pollDiffs = row[12]
			mp.pollAccord = row[13]
			mp.totalPolls = mp.pollDiffs + mp.pollAccord
			retVal.append(mp)
		
		return retVal


	def getSlackers(self, dayStart, dayEnd = None, orderDesc = True):
		"""
		Returns list of person objects ordered by absences. Absences are
		computed for given day range. This kind of query is intended for
		computing absences for day/week/month/?year?
			dayStart	- from when to start computing absences (including
						  start day)
			dayEnd		- the last day in range (the day will be included
						  in stat)
			orderDesc	- default true - the objects will be ordered
						  starting with the highest absences count
		 """
		select = None
		if dayEnd:
			select = """
				SELECT	* FROM (
					SELECT	id AS personId,
							mpId AS personMpId,
							name AS personName,
							surname AS personSurname,
							title AS personTitle,
							titleLast AS personTitleLast,
							birthDate AS personBirthDate,
							email AS personEmail,
							office AS personOffice,
							regionOffice AS personRegionOffice,
							phone AS personPhone,
							slug AS personSlug,
							abs AS absences,
							excs AS excused,
							nay AS votes_nay,
							aye AS votes_aye,
							refrains AS votes_refrains,
							(abs + excs) AS total_absences FROM (
						SELECT	person.id AS id,
								person.mpId AS mpId,
								person.name AS name,
								person.surname AS surname,
								person.title AS title,
								person.titleLast AS titleLast,
								person.birthDate AS birthDate,
								person.email AS email,
								person.office AS office,
								person.regionOffice AS regionOffice,
								person.phone AS phone,
								person.slug AS slug,
								SUM(
									CASE
										vote.result = 'M' THEN 1
										ELSE 0
									END
								) AS abs,
								SUM(
									CASE
										WHEN vote.result = '0' OR
											vote.result = 'X' THEN 1
										ELSE 0
									END
								) AS excs,
								SUM(
									CASE
										WHEN vote.result = 'N' THEN 1
										ELSE 0
									END
								) AS nay,
								SUM (
									CASE
										WHEN vote.result = 'Z' THEN 1
										ELSE 0
									END
								) AS refrains,
								SUM (
									CASE
										WHEN vote.result = 'A' THEN 1
										ELSE 0
									END
								) AS aye
								FROM psp_poll AS poll
									INNER JOIN psp_mpvote AS vote ON
												poll.id = vote.poll_id
									INNER JOIN psp_person AS person ON
												vote.person_id = person.id
								WHERE poll.date = '%d-%02d-%02d'
								GROUP BY vote.result, person.id
					) GROUP BY id ORDER BY total_absences %s
				);
			"""
			order = ''

			if orderDesc:
				order = 'DESC'
			else:
				order = 'ASC'

			select = select % (	dayStart.year, dayStart.month, dayStart.day,
								dayEnd.year, dayStart.month, dayStart.day,
								order
							)
		else:
			select = """
				SELECT	* FROM (
					SELECT	id AS personId,
							mpId AS personMpId,
							name AS personName,
							surname AS personSurname,
							title AS personTitle,
							titleLast AS personTitleLast,
							birthDate AS personBirthDate,
							email AS personEmail,
							office AS personOffice,
							regionOffice AS personRegionOffice,
							phone AS personPhone,
							slug AS personSlug,
							abs AS absences,
							excs AS excused,
							nay AS votes_nay,
							refrains AS votes_refrains,
							aye AS votes_aye,
							(abs + excs) AS total_absences FROM (
						SELECT	person.id AS id,
								person.mpId AS mpId,
								person.name AS name,
								person.surname AS surname,
								person.title AS title,
								person.titleLast AS titleLast,
								person.birthDate AS birthDate,
								person.email AS email,
								person.office AS office,
								person.regionOffice AS regionOffice,
								person.phone AS phone,
								person.slug AS slug,
								SUM(
									CASE
										vote.result = 'M' THEN 1
										ELSE 0
									END
								) AS abs,
								SUM(
									CASE
										WHEN vote.result = '0' OR
											vote.result = 'X' THEN 1
										ELSE 0
									END
								) AS excs,
								SUM(
									CASE
										WHEN vote.result = 'N' THEN 1
										ELSE 0
									END
								) AS nay,
								SUM (
									CASE
										WHEN vote.result = 'Z' THEN 1
										ELSE 0
									END
								) AS refrains,
								SUM (
									CASE
										WHEN vote.result = 'A' THEN 1
										ELSE 0
									END
								) AS aye
								FROM psp_poll AS poll
									INNER JOIN psp_mpvote AS vote ON
										poll.id = vote.poll_id
									INNER JOIN psp_person AS person ON
										vote.person_id = person.id
								WHERE poll.date = '%d-%02d-%02d'
								GROUP BY vote.result, person.id
					) GROUP BY id ORDER BY total_absences %s
				);
			"""
			order = ''

			if orderDesc:
				order = 'DESC'
			else:
				order = 'ASC'

			select = select % (dayStart.year, dayStart.month, dayStart.day, order)

		cursor = connection.cursor()
		cursor.execute(select)
		for row in cursor.fetchall():
			mp = self.createPersonFromRow(row)
			mp.absences = row[12]
			mp.excused = row[13]
			mp.votesNay = row[14]
			mp.refrains = row[15]
			mp.votesAye = row[16]
			mp.totalAbsences = row[17]
			retVal.append(mp)
		
		return retVal


	def countPersonsForDay(self, date):
		"""
		Returns MPs for particular day.
		"""
		select = """
			SELECT DISTINCT person.id
				FROM psp_person AS person
				INNER JOIN psp_group AS party ON party.type LIKE 'KLUB'
				INNER JOIN psp_membership AS partymship ON
							partymship.group_id = party.id AND
							partymship.post LIKE 'člen' AND
							partymship.person_id = person.id AND
							((partymship.end IS NULL AND
								partymship.start <= '%d-%02d-%02d'
							) OR (
							'%d-%02d-%02d' BETWEEN partymship.start AND
									 partymship.end 
							))
				GROUP BY person.id;
		"""
		select = select % (	date.year, date.month, date.day,
							date.year, date.month, date.day
						)
		cursor = connection.cursor()
		cursor.execute(select)
		return len(cursor.fetchall())

class Person(AutoSlug):
	mpId = models.CharField(_("MP ID"), unique = True, max_length = 10, db_index = True)
	name = models.CharField(_("Name"), max_length = 30)
	surname = models.CharField(_("Surname"), max_length = 30)
	title = models.CharField(_("Title"), max_length = 10, blank = True)
	titleLast = models.CharField(	_("Title after Name"),
									max_length = 10,
									blank = True
								)
	birthDate = models.DateField(_("Birthdate"))
	email = models.EmailField(_("Email"))
	homePage = models.URLField(_("Homepage"), blank = True, verify_exists = False)
	office = models.CharField(_("Office"), max_length = 40)
	regionOffice = models.CharField(_("Electoral Ward Office"),
										max_length = 60
									)
	phone = PhoneNumberField(_("Phone Number"))
	slug = models.SlugField(db_index = True, unique = True)
	slugs = {
		'slug' : ('name', 'surname', 'title', 'titleLast')
	}
	objects = PersonManager()

	class Meta:
		verbose_name = _("MP")
		verbose_name_plural = _("MPs")

	class Admin:
		pass
	
	@property
	def sex(self):
		retVal = ''
		try:
			retVal = self._sex
		except AttributeError:
			if self.name.endswith('a') and \
				not self.surname.endswith(u'ý'):
				self._sex = 'F'
			else:
				self._sex = 'M'

			retVal = self._sex

		return retVal
				

	def getTermLinks(self, currentTerm = None):
		"""
		returns list of links for other terms the MP has
		been sitting in parliament.
		"""
		retVal = []
		url = u''

		if currentTerm == None:
			url = _(u'Všechna období')
		else:
			r = reverse('show_mp', args = [ self.slug ])
			formatStr = u'<a href="%s">' + _(u'Všechna období') + '</a>'
			url = formatStr % r

		retVal.append(url)

		for parlMship in self.getParlMemberships():
			url = u''
			term = parlMship.group.term
			content = '%d - %d' % (term.getStart(), term.getEnd(),)

			if currentTerm != None and term == currentTerm:
				url = u'<span>%s</span>' % content
				url += u'<span class="ui-icon ui-icon-triangle-1-w"></span>'
			else:
				url = u'<a href="%s">%s</a>' % (
							reverse('show_mp_term', args = [
											self.slug,
											str(term.getStart()),
											str(term.getEnd())
										]),
							content
						)
			retVal.append(url)
				
		return retVal
		
	def getStatsChartForDates(self, startDate, endDate = None):
		"""
		Returns pie chart with overall poll stats for MP for given date range.
		"""
		ps = self.getPersonWithStats(startDate, endDate)
		
		if ps == None:
			return None

		retVal = Pie3D([	get_percents(ps.votesAye, ps.votesTotal),
							get_percents(ps.votesNay, ps.votesTotal),
							get_percents(ps.votesRefrain, ps.votesTotal),
							get_percents(ps.totalAbsences, ps.votesTotal)
						])
		retVal.label(	_(u'Pro %.2f %%') % get_percents(
												ps.votesAye,
												ps.votesTotal
											),
						_(u'Proti %.2f %%') % get_percents(
													ps.votesNay,
													ps.votesTotal
												),
						_(u'Zdržel %.2f %%') % get_percents(
													ps.votesRefrain,
													ps.votesTotal
												),
						_(u'Chybí %.2f %%') % get_percents(
													ps.totalAbsences,
													ps.votesTotal
												)
					)
		retVal.color('00dd00', 'dd0000', 'dddddd', '0000dd')
		retVal.size(330, 80)
		retVal.title(_('%s. %s - Denní statistika') % (
												self.name[0],
												self.surname)
											)

		return retVal

	def getPresencePieChart(self, startDate, endDate):
		ps = self.getPersonWithStats(startDate, endDate)
		
		if ps == None:
			return None

		presences = ps.votesAye + ps.votesNay + ps.votesRefrain
		retVal = Pie3D([	get_percents(presences, ps.totalPolls),
							get_percents(ps.excused, ps.totalPolls),
							get_percents(ps.absences, ps.totalPolls)
						])
		retVal.label(	_(u'Přítomen') + ' %d' % presences,
						_(u'Omluven') + ' %d' % excused,
						_(u'Chybí') + ' %d' % absences,
					)
		retVal.color('00dd00', 'dd7700', 'dd0000') 
		retVal.size(330, 80)

		return retVal
		
	def getPresencePieChartForDate(self, date):
		retVal = self.getPresencePieChart(startDate = date, endDate = None)

		if retVal != None:
			retVal.title( _(u'Absence %s') % date.strftime('%d %b $y') )

		return retVal

	def getPresencePieChartForTerm(self, term = None):
		retVal = None
		stats = None
		try:
			stats = VoteStats.objects.getStatsForPerson(self, term)
		except ObjectDoesNotExist:
			return None

		presences = stats.votesAye + stats.votesNay + stats.votesRefrain
		totalPolls = presences + stats.absences + stats.excused
		retVal = Pie3D([	get_percents(presences, totalPolls),
							get_percents(stats.excused, totalPolls),
							get_percents(stats.absences, totalPolls)
						])
		retVal.label(	_(u'Přítomen') + ' %d' % presences,
						_(u'Omluven') + ' %d' % stats.excused,
						_(u'Chybí') + ' %d' % stats.absences,
					)
		retVal.color('00dd00', 'dd7700', 'dd0000') 
		retVal.size(330, 80)
		if term == None:
			retVal.title(_(u'Absence celkem'))
		else:
			retVal.title(_(u'Absence %d - %d' % (
										term.getStart(),
										term.getEnd()
									)))

		return retVal

	def getParlMemberships(self):
		retVal = self.membership_set.filter(
							group__type__iexact = 'PARLAMENT',
							post__iregex = '.*POSLAN.*'
						)
		return retVal

	def getGroups(self, term):
		retVal = []
		if term != None:
			retVal = self.membership_set.filter(
							~Q(group__type__iexact = 'PARLAMENT'),
							group__term = term,
							post__iregex = u'.len'
						)
		else:
			retVal = self.membership_set.filter(
							~Q(group__type__iexact = 'PARLAMENT'),
							post__iregex = u'.len'
						)

		return retVal

	def getMshipsForDate(self, date):
		t = TermOfOffice.get_term_for_date(date)
		try:
			retVal = self.membership_set.filter(
									~Q(group__type__iexact = 'PARLAMENT'),
									end__leq = date,
									group__term = t,
									post__iregex = u'.len',
								)
		except FieldError:
			retVal = self.membership_set.filter(
									~Q(group__type__iexact = 'PARLAMENT'),
									end = None,
									group__term = t,
									post__iregex = u'.len',
								)
		return retVal

	def getPartyClub(self, date):
		t = TermOfOffice.get_term_for_date(date)
		try:
			partyMship = self.membership_set.get(
							group__type__iexact = 'KLUB',
							group__term = t,
							post__iregex = u'.len',
							start__lte = date,
							end__gte = date
						)
			retVal = partyMship.group
		except ObjectDoesNotExist:
			partyMship = self.membership_set.get(
							group__type__iexact = 'KLUB',
							group__term = t,
							post__iregex = u'.len',
							end = None
						)
			retVal = partyMship.group
		except MultipleObjectsReturned:
			pass

		return retVal

	def getPartyMships(self, term):
		retVal = self.membership_set.filter(
					group__term = term,
					group__type__iexact = 'KLUB'
				).order_by('start')

		return retVal


	def getLoyalChartForDates(self, group, dateStart, dateEnd = None):
		"""
		Generates pie chart describing loyality of MP to particular group
		(accordance of polling with group). If no group is specified, the MP's
		party club is selected.  dateStart, dateEnd is date range, if dateEnd
		is None only the dateStart day is included into computation.
		"""
		
		mp = self.getAccordanceWithGroup(group, dateStart, dateEnd)

		same = get_percents(mp.pollAccord, mp.totalPolls)
		diff = 100.0 - same
		retVal = Pie3D( [same, diff] )
		if group.type == 'KLUB':
			title = _(u'%s. %s - loajalita s %s') % (
												self.name[0],
												self.surname,
												group.getPartyName()
											)
		else:
			title = _(u'%s. %s - loajalita %s') % (
												self.name[0],
												self.surname,
												group.name
											)

		retVal.title(title)
		retVal.label(_(u'Stejně (%.2f%%)' % same), _(u'Jinak (%.2f%%)' % diff))
		retVal.color('007700', '770000')
		retVal.size(330, 80)

		return retVal


	def getLoyalChart(self, term, group = None):
		poll = Poll.objects.filter(meeting__term = term).order_by('date')
		if poll.count() == 0:
			retVal = None
		elif poll.count() == 1:
			dayStart = poll[0].date
			retVal = self.getLoyalChartForDates(dayStart = dayStart, group = group)
		else:
			dayStart = poll[0].date
			dayEnd = poll[len(poll) - 1].date
			retVal = self.getLoyalChartForDates(
								dateStart = dayStart,
								dateEnd = dayEnd,
								group = group
							)
		return retVal
	

	def getAccordanceWithGroup(self, group, dayStart, dayEnd = None):
		"""
		Retrieves a poll accordance for particular person with particular
		group.  Person object is extended by a new 'accordance' attribute.
		Accordance attribute tells the percent of polls polled in accordance
		with group.
			group		- group to compute accordance with
			dayStart 	- date object defining start day
			dayEnd		- date object defining end day, it is optional
						  parameter, if no dayEnd is specified, then accordance
						  for desired dayStart date is retreived only.
		returns a copy of 'self' (Person instance) extended by these attributes:
			totalPolls	- total number of polls for given date range
			pollAccord	- number of polls in accordance with group
			pollDiffs	- number of polls differnt <-> totalPolls - accordance
		None is returned on errors or if there are no polls for given date range
		"""
		groupId = group_to_id(group)
		select = None
		totalPolls = 0
		if dayEnd != None:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result <> vote.result THEN 1
								ELSE 0
							END
						) AS different,
						SUM(
							CASE
								WHEN gs.result == vote.result THEN 1
								ELSE 0
							END
						) AS same
						FROM psp_poll AS poll
							INNER JOIN psp_mpvote AS vote ON
										poll.id = vote.poll_id AND
										vote.person_id = %d
							INNER JOIN psp_groupstats AS gs ON
										poll.id = gs.poll_id AND
										gs.group_id = %d
						WHERE poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d';
			"""
			select = select % (	self.id,
								groupId,
								dayStart.year, dayStart.month, dayStart.day,
								dayEnd.year, dayEnd.month, dayEnd.day,
							)
		else:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result <> vote.result THEN 1
								ELSE 0
							END
						) AS different,
						SUM(
							CASE
								WHEN gs.result == vote.result THEN 1
								ELSE 0
							END
						) AS same
						FROM psp_poll AS poll
							INNER JOIN psp_mpvote AS vote ON
										poll.id = vote.poll_id AND
										vote.person_id = %d
							INNER JOIN psp_groupstats AS gs ON
										poll.id = gs.poll_id AND
										gs.group_id = %d
						WHERE poll.date = '%d-%02d-%02d';
			"""
			select = select % (	self.id,
								groupId,
								dayStart.year, dayStart.month, dayStart.day,
							)


		cursor = connection.cursor()
		cursor.execute(select)
		rows = cursor.fetchall()

		if len(rows) != 1:
			return None

		row = rows[0]
		retVal = Person(self)
		retVal.pollDiffs = row[0]
		retVal.pollAccord = row[1]
		retVal.totalPolls = retVal.pollDiffs + retVal.pollAccord

		return retVal


	def getPersonWithStats(self, dayStart, dayEnd = None):
		"""
		Returns a copy of 'self' (person object) with vote stats attributes for
		particular date range. If dayEnd is not specified, then stats for
		dayStart are returned. If return value is None, then there are no stats
		for chosen day/range.
		"""
		if dayEnd == None:
			select = """
				SELECT	SUM(
							CASE
								WHEN vote.result = 'M' THEN 1
								ELSE 0
							END
						) AS abs,
						SUM(
							CASE
								WHEN vote.result = '0' OR
									vote.result = 'X' THEN 1
								ELSE 0
							END
						) AS excs,
						SUM(
							CASE
								WHEN vote.result = 'N' THEN 1
								ELSE 0
							END
						) AS nay,
						SUM (
							CASE
								WHEN vote.result = 'Z' THEN 1
								ELSE 0
							END
						) AS refrains,
						SUM (
							CASE
								WHEN vote.result = 'A' THEN 1
								ELSE 0
							END
						) AS aye
						FROM psp_poll AS poll
							INNER JOIN psp_mpvote AS vote ON
										poll.id = vote.poll_id AND
										vote.person_id = %d
						WHERE poll.date = '%d-%02d-%02d';
			"""
			select = select % (	self.id,
								dayStart.year, dayStart.month, dayStart.day
							)

		else:
			select = """
				SELECT	SUM(
							CASE
								WHEN vote.result = 'M' THEN 1
								ELSE 0
							END
						) AS abs,
						SUM(
							CASE
								WHEN vote.result = '0' OR
									vote.result = 'X' THEN 1
								ELSE 0
							END
						) AS excs,
						SUM(
							CASE
								WHEN vote.result = 'N' THEN 1
								ELSE 0
							END
						) AS nay,
						SUM (
							CASE
								WHEN vote.result = 'Z' THEN 1
								ELSE 0
							END
						) AS refrains,
						SUM (
							CASE
								WHEN vote.result = 'A' THEN 1
								ELSE 0
							END
						) AS aye
						FROM psp_poll AS poll
							INNER JOIN psp_mpvote AS vote ON
										poll.id = vote.poll_id AND
										vote.person_id = %d
						WHERE poll.date BETWEEN '%d-%02d-%02d' AND
												'%d-%02d-%02d';
			"""
			select = select % (	self.id,
								dayStart.year, dayStart.month, dayStart.day,
								dayEnd.year, dayEnd.month, dayEnd.day
							)

		cursor = connection.cursor()
		cursor.execute(select)
		rows = cursor.fetchall()

		if len(rows) != 1:
			return None

		row = rows[0]
		retVal = Person(self)
		retVal.absences = row[0]
		retVal.excused = row[1]
		retVal.totalAbsences = row[0] + row[1]
		retVal.votesNay = row[2]
		retVal.votesRefrain = row[3]
		retVal.votesAye = row[4]
		retVal.votesTotal = sum(row)
		retVal.percAbsences = get_percents(	retVal.totalAbsences,
												retVal.votesTotal
											)

		return retVal

	
pre_save.connect(makeSlug, Person)


class PersonDetail(models.Model):
	region = models.ForeignKey(Division, blank = True, null = True)
	person = models.ForeignKey(Person)
	term = models.ForeignKey(TermOfOffice)
	photo = models.ImageField(	_("Photo"),
								upload_to = settings.MP_PHOTOS
							)
	class Meta:
		verbose_name = _("Person's Additional Info")
		verbose_name_plural = _("Person's Additional Info")

	
class GroupManager(models.Manager):
	def getGroupWithStats(self, term, qType, poll):
		retVal = []
		for g in Group.objects.filter(
							Q(term = term),
							qType).order_by('name'):
			try:
				g.stat = GroupStats.objects.get(group = g, poll = poll)
			except ObjectDoesNotExist:
				g.stat = None

			subGroups = []

			for sg in g.children.all():
				try:
					sg.stat = GroupStats.objects.get(
									group = sg,
									poll = poll
								)
				except ObjectDoesNotExist:
					g.stat = None

				subGroups.append(sg)

			g.subGroups = subGroups
			retVal.append(g)

		return retVal


	def getGroupWithStatsArg(self, poll, qObjs):
		retVal = []
		for g in Group.objects.filter(*qObjs).order_by('name'):
			try:
				g.stat = GroupStats.objects.get(group = g, poll = poll)
			except ObjectDoesNotExist:
				g.stat = None

			subGroups = []

			for sg in g.children.all():
				try:
					sg.stat = GroupStats.objects.get(
									group = sg,
									poll = poll
								)
				except ObjectDoesNotExist:
					sg.stat = None

				subGroups.append(sg)

			g.subGroups = subGroups
			retVal.append(g)

		return retVal

			
class Group(AutoSlug):
	term = models.ForeignKey(TermOfOffice)
	members = models.ManyToManyField(	Person,
										through='Membership',
										blank = True,
										null = True
									)
	parentGroup = models.ForeignKey(	'self',
									related_name = 'children',
									blank = True,
									null = True
								)
	groupId = models.CharField(_("Group Id"), max_length = 20)
	name = models.CharField(_("Name"), max_length = 512)
	origName = models.CharField(_("Name"), max_length = 1024)
	type = models.CharField(_("Club Type"), max_length = 50, blank = True)
	color = models.CharField(_("Colour"), max_length = 6, blank = True)
	icon = models.ImageField(_("Club Icon"),
								upload_to = settings.GROUP_ICONS,
								blank = True
							)
	slug = models.SlugField(db_index = True)
	slugs = {
		'slug' : ('name', )
	}

	objects = GroupManager()

	class Meta:
		verbose_name = _("MP Organ (party/board/...)")
		verbose_name_plural = _("MP Organs (parties/boards/...)")
		unique_together = (('term', 'groupId',),)

	class Admin:
		pass


	def getMembersCommon(self,
							dateStart = None,
							dateEnd = None,
							orderArg = None,
							distinct = True):
		"""
		Returns list of person objects - members of group.  If no date is
		specified returns summary for complete term, otherwise returns member
		list valid for particular date range. If dateStart only is specified,
		then actual list for given date is returned.

		Each person object in list returned is extended by these attributes:
			mship,
			days,
		and:
			age,
			partyClub,
			division,
			... 
			[ all attributes inserted by PersonManager.commonSelect ]
		"""

		mships = None
		if dateStart == None and dateEnd == None:
			mships = self.membership_set.all()
		elif dateStart != None and dateEnd == None:
			mships = self.membership_set.filter(
							Q(end = None) | Q(end__gte = dateStart),
							start__lte = dateStart
						)
		elif dateStart != None and dateEnd != None:
			mships = self.membership_set.filter(
							~Q(end = None),
							Q(end__gte = dateEnd),
							start__lte = dateStart
						)
		else:
			return []

		if orderArg:
			mships = mships.order_by(orderArg)

		#
		# we have to fake distinct by person, if we are asked to do so
		#
		if distinct:
			tmp = {}
			for m in mships:
				tmp[m.person.id] = m
			
			mships = []
			for k in tmp.keys():
				mships.append(tmp[k])

		retVal = []

		for m in mships:
			person = Person.objects.getPerson(m.person, self.term)
			person.mship = m
			person.start = m.start
			person.end = m.end
			person.post = m.post
			if m.end == None:
				person.days = (datetime.date.today() - m.start).days
			else:
				person.days = (m.end - m.start).days

			retVal.append(person)

		return retVal

	def getMembersByAge(self, desc = True, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = self.getMembersCommon(dateStart, dateEnd,
										distinct = distinct
									)
		if desc:
			retVal.sort(lambda a, b: a.age - b.age)
		else:
			retVal.sort(lambda a, b: b.age - a.age)

		return retVal

	def getMembersByParty(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = self.getMembersCommon(dateStart, dateEnd,
										distinct = True
									)
		if desc:
			retVal.sort(lambda a, b: a.partyName > b.partyName)
		else:
			retVal.sort(lambda a, b: a.partyName < b.partyName)

		return retVal

	def getMembersByName(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = None
		if desc:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'-person__name',
											distinct = distinct
										)
		else:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'person__name',
											distinct = distinct
										)
		return retVal

	def getMembersBySurname(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = None
		if desc:
			retVal = self.getMembersCommon(	
											dateStart,
											dateEnd,
											'-person__surname',
											distinct = distinct
										)
		else:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'person__surname',
											distinct = distinct
										)
			
		return retVal
	
	def getMembersByDivision(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = self.getMembersCommon(dateStart, dateEnd, distinct = distinct)
		if desc:
			retVal.sort(lambda a, b: a.divisionName > b.divisionName)
		else:
			retVal.sort(lambda a, b: a.divisionName < b.divisionName)

		return retVal

	def getMembersByDays(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = self.getMembersCommon(dateStart, dateEnd, distinct = distinct)
		if desc:
			retVal.sort(lambda a, b: a.days > b.days)
		else:
			retVal.sort(lambda a, b: a.days < b.days)

		return retVal

	def getMembersByStart(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = None
		if desc:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'-start',
											distinct = distinct
										)
		else:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'start',
											distinct = distinct
										)
		return retVal

	def getMembersByEnd(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = None
		if desc:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'-end',
											distinct = distinct
										)
		else:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'end',
											distinct = distinct
										)
		return retVal

	def getMembersByPost(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = None
		if desc:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'-post',
											distinct = distinct 
										)
		else:
			retVal = self.getMembersCommon(
											dateStart,
											dateEnd,
											'post',
											distinct = distinct
										)
		return retVal

	def getMembersByAbsences(self, desc = False, dateStart = None,
							dateEnd = None, distinct = True):
		retVal = self.getMembersCommon(dateStart, dateEnd, distinct = distinct)

		if desc:
			retVal.sort(lambda a, b: int(a.percAbsences - b.percAbsences))
		else:
			retVal.sort(lambda a, b: int(b.percAbsences - a.percAbsences))
		

		return retVal

	def getGroupPartyProfile(	self,
								dateStart = None,
								dateEnd = None):
		"""
		Get group breakdown by party. Returns list party groups.  Each party
		group in list is extended by these parameters:
			mpCount
			group - which is the self (the group we are braking down to parties rep)
			totalCount - number of members in self
			percent - relative percent of party members in self

		dateStart, dateEnd define date range we want to get data for.
		If both are None function will process data for term, if dateStart
		is defined then state to particular dateStart date is returned
		"""
		if self.type == 'KLUB':
			return None

		select = None
		if dateStart == None and dateEnd == None:
			select = """
				SELECT groupId, SUM(num) FROM (
					SELECT	partyMship.group_id AS groupId,
							COUNT(DISTINCT mp.id) AS num
						FROM	psp_person AS mp,
								psp_membership AS mship
						JOIN psp_group AS party ON
								party.type LIKE 'KLUB' AND
								party.term_id = %d
						JOIN psp_membership AS partyMship ON
								mp.id = partyMship.person_id AND
								partyMship.group_id = party.id
						WHERE mship.group_id = %d AND mship.person_id = mp.id
						GROUP BY mp.id ORDER BY partyMship.group_id
					)
					GROUP BY groupId;
			"""
			select = select % (self.term.id, self.id)
		elif dateStart != None and dateEnd == None:
			date = '%d-%02d-%02d' % (	dateStart.year,
										dateStart.month,
										dateStart.day
									)
			select = """
				SELECT groupId, SUM(num) FROM (
					SELECT	partyMship.group_id AS groupId,
							COUNT(DISTINCT mp.id) AS num
						FROM	psp_person AS mp,
								psp_membership AS mship
						JOIN	psp_group AS party ON
								party.type LIKE 'KLUB' AND party.term_id = %d
						JOIN	psp_membership AS partyMship ON
								mp.id = partyMship.person_id AND
								partyMship.group_id = party.id AND
								(	(partyMship.end IS NULL AND
									partyMship.start <= '%s') OR
									('%s' BETWEEN partyMship.start AND
									partyMship.end)
								)
						WHERE mship.group_id = %d AND
								mship.person_id = mp.id AND
								(	(mship.end IS NULL AND
									mship.start <= '%s') OR
									('%s' BETWEEN mship.start AND mship.end)
								)
						GROUP BY mp.id ORDER BY partyMship.group_id
					)
					GROUP BY groupId;
			"""
			select = select % (	self.term.id,
								date, date,
								self.id,
								date, date
							)
		elif dateStart != None and dateEnd != None:
			start = '%d-%02d-%02d' % (	dateStart.year,
										dateStart.month,
										dateStart.day
								)
			end = '%d-%02d-%02d' % (	dateEnd.year,
										dateEnd.month,
										dateEnd.day
								)
			select = """
				SELECT groupId, SUM(num) FROM (
					SELECT	partyMship.group_id AS groupId,
							COUNT(DISTINCT mp.id) AS num
						FROM psp_person AS mp, psp_membership AS mship
						JOIN	psp_group AS party ON
								party.type LIKE 'KLUB' AND
								party.term_id = %d
						JOIN	psp_membership AS partyMship ON
								mp.id = partyMship.person_id AND
								partyMship.group_id = party.id AND
								((partyMship.end IS NULL AND
									(partyMship.start BETWEEN '%s' AND '%s' OR
										partyMship.start <= '%s'))
									OR
									(partyMship.start BETWEEN '%s' AND '%s' OR
									partyMship.end BETWEEN '%s' AND '%s') OR
								(partyMship.start <= '%s'))
						WHERE	mship.group_id = %d AND
								mship.person_id = mp.id AND
								((mship.end IS NULL AND
									(mship.start BETWEEN '%s' AND '%s' OR
									mship.start <= '%s'))
								OR
									(mship.start BETWEEN '%s' AND '%s' OR
										mship.end BETWEEN '%s' AND '%s') OR
										(mship.start <= '%s')
								)
						GROUP BY mp.id ORDER BY partyMship.group_id
					)
					GROUP BY groupId;
			"""
			select = select % (	self.term.id,
								start, end, start,
								start, end, start, end,
								start,
								self.id,
								start, end, start,
								start, end, start, end,
								start,
							)
		else:
			raise ObjectDoesNotExist

		
		cursor = connection.cursor()
		cursor.execute(select)
		rows = cursor.fetchall()
		retVal = []
		total = 0

		for row in rows:
			groupId = row[0]
			mpCount = row[1]
			total += mpCount
			group = Group.objects.get(id = groupId)
			group.mpCount = mpCount
			group.group = self
			retVal.append(group)

		for g in retVal:
			g.totalCount = total
			g.percent = get_percents(g.mpCount, total)
			
		return retVal
			
	def getMfRatio(self):
		males = 0
		females = 0
		members = None
		members = self.membership_set.all().select_related().distinct('person')

		male = 0
		female = 0
		for member in members:
			if member.person.sex == 'F':
				females += 1
			else:
				males += 1

		return males, females

	def getMfPie(self):
		"""
		Returns pie chart representing ratio between men and women in
		parliament.
		"""
		male, female = self.getMfRatio()
		pm = get_percents(male, male + female)
		pf = get_percents(female, male + female)
		retVal = Pie3D([pm, pf])
		retVal.label(
				_(u'Muži') + ' %d' % male,
				_(u'Ženy') + ' %d' % female
			)
		retVal.color('000077', '770000') 
		retVal.title(_(u'Poměr mužů a žen') + '(%d - %d)' % (
										self.term.getStart(),
										self.term.getEnd())
									)
		retVal.size(330, 130)

		return retVal
		
	def getPartyName(self):
		"""
		the mpClub.name usually looks like
			Poslanecký klub Občasnské demokratické strany

		the function turns it into abbreviation:
			ODS

		there are some corner cases:
			Nezávislí

		sometimes Poslanecký klub is not part of name

		TODO: make abbreviation as a class attribute in order
		to save cycles
		"""
		#work around Křesťansko demokratická strana-českosloven...
		name = self.origName.replace('-', ' - ')
		pattern = re.compile(u'.*Poslanecký klub(?P<name>.*)', re.U)
		match = pattern.match(name)
		retVal = u''
		if match:
			name = match.group('name')
		
		for i in name.split():
			if i == 'a' or i == 'A':
				continue
			retVal += i[0].upper()

		if retVal == 'N':
			retVal = _(u'Nezávislí')

		return retVal.strip()


	def getAvgAge(self):
		retVal = 0
		totalAge = 0
		count = 0

		members = self.membership_set.all().distinct('person')
		for member in members:
			mp = Person.objects.get(id = member.person.id)
			age = self.term.getStart() - mp.birthDate.year
			totalAge += age
			count += 1

		if count != 0:
			retVal = totalAge / count

		return retVal
		

	def getPartyPie(self, dateStart = None, dateEnd = None):
		partyProfile = self.getGroupPartyProfile(dateStart, dateEnd)
	
		if partyProfile == None:
			return None

		pieData = [ party.mpCount for party in partyProfile ]
		retVal = Pie3D(pieData)

		label = [ '%s (%.2f%%)' % (	party.getPartyName(),
									party.percent
								)  for party in partyProfile 
				]
		retVal.label(*label)

		colors = ''
		for party in partyProfile:
			colors += party.color + '|'
		retVal.color(colors.strip('|'))

		if dateStart == None and dateEnd == None:
			title = u'%s (%d - %d)' % (
							self.name,
							self.term.getStart(),
							self.term.getEnd()
						)
		elif dateStart != None and dateEnd == None:
			title = u'%s (%s)' % (
							self.name,
							dateStart.strftime("%d. %m. %Y")
						)
		else:
			title = u'%s (%s - %s)' % (
							self.name,
							dateStart.strftime("%d. %m. %Y"),
							dateEnd.strftime("%d. %m. %Y")
						)

		retVal.title(title)
		retVal.size(400, 130)

		return retVal

	
	def getResultChart(self, dateStart):
		"""
		Returns chart for polls in date range. if no dateEnd is specified chart
		for desired date is shown.
		"""
		
		gWithStats = GroupStats.objects.getStats(self, dateStart, dateEnd)
		
		aye = get_percents(gWithStats.aye, gWithStats.totalCount),
		nay = get_percents(gWithStats.nay, gWithStats.totalCount),
		refrains = get_percents(gWithStats.refrains, gWithStats.totalCount),
		absences = get_percents(gWithStats.absen, gWithStats.totalCount),
		retVal = Pie3D([ aye, nay, refrains, absences ])
		retVal.label(	_(u'Pro %.2f %%') % aye,
						_(u'Proti %.2f %%') % nay,
						_(u'Zdržel %.2f %%') % refrains,
						_(u'Chybí %.2f %%') % absences
					)
		retVal.color('00dd00', 'dd0000', 'dddddd', '0000dd')
		retVal.size(330, 80)
		if self.type == 'KLUB':
			retVal.title(_('%s - Denní statistika') % self.getPartyName())
		else:
			retVal.title(_('%s - Denní statistika') % self.name)

		return retVal


	def getAccordanceWithGroup(	self,
								otherGroup,
								dateStart = None,
								dateEnd = None
							):
		groupAccord = GroupStats.objects.getAccordanceWithGroup(
							self, otherGroup,
							dateStart, dateEnd
						)
		same = get_percents(groupAccord.same, groupAccord.total)
		diff = 100 - same
		retVal = Pie3D([ same, diff ])

		title = ''
		if self.type == 'KLUB':
			title = _(u'%s - shoda s ') % (self.getPartyName())
		else:
			title = _(u'%s - shoda s ') % (self.name)

		if otherGroup.type == 'KLUB':
			title += otherGroup.getPartyName()
		else:
			title += otherGroup.name
		retVal.title(title)
		retVal.label(_(u'Stejně (%.2f%%)' % same), _(u'Jinak (%.2f%%)' % diff))
		retVal.color('007700', '770000')
		retVal.size(330, 80)
		
		return retVal


	def getPresencePieChart(self, term = None):
		stats = GroupStats.objects.filter(group = self)

		if stats.count() == 0:
			return None

		absences = 0
		for s in stats:
			if s.result == '' or s.result == ' ':
				s.getResult()

			if s.result == '0':
				absences += 1

		total = stats.count()
		presences = total - absences
		total = total * 1.0

		print presences, ' ', absences
		retVal = Pie3D([	get_percents(presences, total),
							get_percents(absences, total)
					])
		retVal.label(	_(u'Přítomni') + ' %.2f%%' % get_percents(
														presences,
														total
													),
						_(u'Chybí') + ' %.2f%%' % get_percents(absences, total)
					)
		
		if self.type == 'KLUB':
			retVal.title(_(u'%s - absence %d - %d') % (
									self.getPartyName(),
									self.term.getStart(),
									self.term.getEnd()
								)
							)
		else:
			retVal.title(_(u'%s Absence %d - %d') % (
									self.name,
									self.term.getStart(),
									self.term.getEnd()
								)
							)
		retVal.color('00dd00', 'dd0000') 
		retVal.size(330, 80)

		return retVal


	"""
	@staticmethod
	def get_boards(term):
		retVal = []
		boards = Group.objects.filter(
							term = term,
							type__iexact = u'VÝBOR'
						).order_by('name')
		for i in boards:
			record = {}
			record['parent'] = i
			record['childern'] = Group.objects.filter(
											term = term,
											parentGroup = i
										).order_by('name')
			retVal.append(record)

		return retVal
	"""



pre_save.connect(makeSlug, Group)


class Membership(models.Model):
	group = models.ForeignKey(Group)
	person = models.ForeignKey(Person)
	post = models.CharField(_("Post"), max_length = 30)
	start = models.DateField(_("Start Date"))
	end = models.DateField(_("End Date"), blank = True, null = True)

	class Meta:
		verbose_name = _("MP Organ Membership")
		verbose_name_plural = _("MP Organs Membership")

	class Admin:
		pass

	def getStart(self):
		return self.start

	def getEnd(self):
		if self.end == None:
			return datetime.datetime.today()

		return self.end
		
	def getDuration(self):
		if self.end == None:
			today = datetime.datetime.today()
			return today - self.start

		return self.end - self.start


class Meeting(models.Model):
	term = models.ForeignKey(TermOfOffice)
	meetingNo = models.CharField(_("Meeting No."), max_length = 8)
	start = models.DateField(_("Start Date"))
	end = models.DateField(_("End Date"))
	name = models.CharField(_("Session Name"), max_length = 80)
	url = models.URLField(_("Link To Meeting Agenda"), verify_exists = False)

	class Meta:
		verbose_name = _("Meeting")
		verbose_name_plural = _("Meetings")
	
	class Admin:
		pass

	def getPollsForMP(self, mp):
		votes = MpVote.objects.filter(
					poll_meeting = self,
					result__iregex = 'A|N|Z',
					person = mp
				)
		return votes
	
	def getPollAbsencesForMP(self, mp):
		votes = MpVote.objects.filter(
					poll_meeting = self,
					result__iregex = '0|M|X',
					person = mo
				)
	
	def getMeetingDates(self):
		retVal = []
		day = datetime.timedelta(days = 1)
		date = self.start

		while date <= end:
			retVal.append(date)

		return retVal


	@staticmethod
	def get_meetings(start, end):
		retVal = Meeting.objects.filter(start__gte = start, end__leq = end)

	def get_meetings_for_month(year, month):
		meetings = get_meetings()


class Poll(AutoSlug):
	meeting = models.ForeignKey(Meeting)
	name = models.CharField(_("Poll Title"), max_length = 80)
	pollId = models.CharField(_("Poll Id"), max_length = 30, db_index = True)
	verdict = models.CharField(_("Verdict"), max_length = 20)
	votesAye = models.IntegerField(	_("Votes For"),
									db_column = 'vote_aye'
								)
	votesNay = models.IntegerField(	_("Votes Against"),
									db_column = 'vote_nay'
								)
	votesRefrain = models.IntegerField(	_("Votes Refrained"),
									db_column = 'refrain'
								)
	absences = models.IntegerField(	_("Absentees"),
									db_column = 'absentees'
								)
	excused = models.IntegerField(_("Excused"))
	slug = models.SlugField(db_index = True)
	resultsLink = models.URLField(	_("Link to Poll Result"),
									verify_exists = False,
									blank = True
								)
	stenoLink = models.URLField(	_("Link to steno record"),
									verify_exists = False,
									blank = True
							)
	date = models.DateField(_("Poll date"), blank = True)
	slugs = {
		'slug' : ('name', )
	}

	class Meta:
		verbose_name = _("Poll")
		verbose_name_plural = _("Polls")

	class Admin:
		pass

	def resultPie(self):
		votes = MpVote.objects.filter(poll = self)
		votesAye = votes.filter(result__iexact = 'A').count() * 1.0
		votesNay = votes.filter(result__iexact = 'N').count() * 1.0
		refrains = votes.filter(result__iexact = 'Z').count() * 1.0
		absences = votes.filter(result__iregex = '0|X|M').count() * 1.0
		votesTotal = votes.count() * 1.0

		retVal = None
		
		if votesTotal != 0:
			retVal = Pie3D([ votesAye, votesNay, refrains, absences ])
			retVal.color(	'007700',
							'770000',
							'007777',
							'777700'
					)
			retVal.label(	_(u'Pro %d%%') % int(get_percents(votesAye, votesTotal)),
							_(u'Proti %d%%') % int(get_percents(votesNay, votesTotal)),
							_(u'Zdrželo %d%%') % int(get_percents(refrains, votesTotal)),
							_(u'Chybělo %d%%') % int(get_percents(absences, votesTotal))
						)
			retVal.size(370, 120)
		
		return retVal

	def bumpPoll(self, result):
		"""
		Bumps poll - result is code for result for particular MP.
			'A' - adds one vote to votesAye
			'N' - adds one vote to votesNay
			'Z' - adds one vote to votesRefrain
			'0' - adds one vote to absences
			'X' 
			'M' - adds one vote for excused
		"""
		if result == 'A':
			self.votesAye += 1
		elif result == 'N':
			self.votesNay += 1
		elif result == 'Z':
			self.votesRefrain += 1
		elif result == '0' or v.result == 'X':
			self.absences += 1
		elif result == 'M':
			self.excused += 1

		return

	
class MpVoteManager(models.Manager):
	def getVoteStats(self, poll):
		"""
		Returns unsavable VoteStats object instance for given poll.
		"""
		pollId = poll_to_id(poll)
		select = """
			SELECT	SUM(	CASE
								WHEN mpvote.result = 'A' THEN 1
								ELSE 0
							END
					) AS votes_aye,
					SUM(	CASE
								WHEN mpvote.result = 'N' THEN 1
								ELSE 0
							END
					) AS votes_nay,
					SUM(	CASE
								WHEN mpvote.result = 'Z' THEN 1
								ELSE 0
					) AS votes_refrains,
					SUM(	CASE
								WHEN mpvote.result = '0' OR
									result = 'X' THEN 1
								ELSE 0
							END
					) AS absences,
					SUM(	CASE
								WHEN mpvote.result = 'M' THEN 1
							ELSE 0
					) AS absences_excused
				FROM psp_mpvote mpvote
				WHERE mpvote.poll_id = %d
		"""
		select = select % pollId
		cursor = connection.cursor()
		cursor.execute(select)
		rows = cursor.fetchall()

		if len(rows) != 1:
			return None

		row = rows[0]
		retVal = VoteStats()
		retVal.votesAye = row[0]
		retVal.votesNay = row[1]
		retVal.votesRefrains = row[2]
		retVal.absences = row[3]
		retVal.excused = row[4]
		retVal.totalAbsences = row[5]
		return retVal
		

class MpVote(AutoSlug):
	poll = models.ForeignKey(Poll, db_index = True)
	person = models.ForeignKey(Person, db_index = True)
	result = models.CharField(_("Result"), max_length = 20)

	class Meta:
		verbose_name = _("Vote")
		verbose_name_plural = _("Votes")
		unique_together = (('poll', 'person',),)

	class Admin:
		pass


	def save(self):
		"""
		Update votestats on creation.

		it will call the parent's save method, if everything works, then it
		will try to update also voteStat for person.

		if there is no VoteStat for person in given term, it will create one

		TBD: we can update poll instance as well if we want to.
		"""
		super(AutoSlug, self).save()

		mpStats = VoteStats.objects.get(	person = self.person,
											term = self.poll.meeting.term
									)
		if self.result == 'A':
			mpStats.votesAye += 1
		elif self.result == 'N':
			mpStats.votesNay += 1
		elif self.result == 'Z':
			mpStats.votesRefrain += 1
		elif self.result == 'M':
			mpStats.excused += 1
		else:
			mpStats.absences += 1

		mpStats.save()

		return

	def delete(self):
		"""
		It gets vote code first, then it will delete instance from DB.
		FInaly it will update VoteStats object for given person, if such
		object exists.

		TBD: we can update poll instance as well if we want to.
		"""
		result = self.result
		mpStats = VoteStats.objects.get(	person = self.person,
											term = self.poll.meeting.term
									)

		super(AutoSlug, self).delete()

		if self.result == 'A':
			mpStats.votesAye -= 1
		elif self.result == 'N':
			mpStats.votesNay -= 1
		elif self.result == 'Z':
			mpStats.votesRefrain -= 1
		elif self.result == 'M':
			mpStats.excused -= 1
		else:
			mpStats.absences -= 1

		mpStats.save()

		return



	def getResult(self):
		"""
		Returns poll result in human readable form.
		"""
		if self.result == 'A':
			return _(u'Pro')
		elif self.result == 'N':
			return _(u'Proti')
		elif self.result == 'Z':
			return _(u'Zdržel')
		elif self.result == '0':
			return _(u'Chybí')
		else:
			return _(u'Omluven')

		
class VoteStatsManager(models.Manager):
	def getStatsForPerson(self, person, term = None):
		"""
		Returns votestats for given person and term. if no term is passed
		functions returns VoteStats object, which has its votes_* attribites
		aggregated for all terms. Such 'aggregated' object can not be saved,
		its term attribute is set to None.
		"""
		retVal = None

		personId = person_to_id(person) 

		if term == None:
			select = """
				SELECT	id,
						person_id,
						SUM(votes_aye),
						SUM(votes_nay),
						SUM(votes_refrains),
						SUM(absences),
						SUM(excused),
						SUM(absences + excused),
						SUM(votes_aye + votes_nay + votes_refrains)
						FROM psp_votestats
						WHERE person_id = %d;
				"""
			select = select % personId
			cursor = connection.cursor()
			cursor.execute(select)
			for row in cursor.fetchall()[:1]:
				retVal = self.model(
							id = row[0],
							person_id = row[1],
							votesAye = row[2],
							votesNay = row[3],
							votesRefrain = row[4],
							absences = row[5],
							excused = row[6]
						)
				retVal.totalAbsences = row[7]
				retVal.presences = row[8]
		else:
			termId = term_to_id(term)
			retVal = self.get(person__id = personId, term__id = termId)
			if retVal != None:
				retVal.totalAbsences = retVal.absences + retVal.excused
				retVal.presences = retVal.votesAye + retVal.votesNay
				retVal.presences += retVal.votesRefrain

		if retVal == None:
			raise ObjectDoesNotExist

		return retVal


	def getStatsForTerm(self, term = None):
		"""
		Aggregates stats for all MPs in given term. If no term is passed
		returns aggregation for all terms. Function returns VoteStat object
		without any reference to particular person. The returned VoteStat
		object can not be saved.
		"""
		retVal = None
		cursor = None
		if term == None:
			select = """
				SELECT	SUM(votes_aye),
						SUM(votes_nay),
						SUM(votes_refrains),
						SUM(absences),
						SUM(excused),
						SUM(absences + excused),
						SUM(votes_aye + votes_nay + votes_refrains)
						FROM psp_votestats
			"""
		else:
			termId = term_to_id(term)
			select = """
				SELECT	SUM(votes_aye),
						SUM(votes_nay),
						SUM(votes_refrains),
						SUM(absences),
						SUM(excused),
						SUM(absences + excused),
						SUM(votes_aye + votes_nay + votes_refrains)
						FROM psp_votestats
						WHERE term_id = %d
			"""
			select = select % termId

		cursor = connection.cursor()
		cursor.execute(select)

		for row in cursor.fetchall()[:1]:
			retVal = self.model(
						votesAye = row[0],
						votesNay = row[1],
						votesRefrain = row[2],
						absences = row[3],
						excused = row[4]
					)
			retVal.totalAbsences = row[5]
			retVal.presences = row[6]
			retVal.term = term
		
		return retVal


	def getLoyalStatsPersonTerm(self, person, group, term):
		personId = term_to_id(person)
		groupId = group_to_id(group)
		termId = term_to_id(term)


class VoteStats(models.Model):
	person = models.ForeignKey(Person, db_index = True)
	term = models.ForeignKey(TermOfOffice)
	votesAye = models.IntegerField(_("Votes For"), db_column = 'votes_aye')
	votesNay = models.IntegerField(_("Votes Against"), db_column = 'votes_nay')
	votesRefrain = models.IntegerField(	_("Votes Refraiend"),
										 db_column = 'votes_refrains'
									)
	absences = models.IntegerField(_("Absences"))
	excused = models.IntegerField(_("Excused Absences"))

	objects = VoteStatsManager()

	class Meta:
		verbose_name = _("Aggregated stats for MP")
		verbose_name_plural = _("Aggregated stats for MPs")

	class Admin:
		pass


class GroupStatsManager(models.Manager):
	def getStats(self, group, dateStart = None, dateEnd = None):
		groupId = group_to_id(group)
		g = Group.objects.get(id = groupId)
		retVal = GroupStats()

		select = None
		if dateStart == None and dateEnd == None:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result = 'A' THEN 1
								ELSE 0
							END
						) AS aye,
						SUM(
							CASE
								WHEN gs.result = 'N' THEN 1
								ELSE 0
							END
						) AS nay,
						SUM(
							CASE
								WHEN gs.result = 'Z' THEN 1
								ELSE 0
							END
						) AS refrains,
						SUM(
							CASE
								WHEN gs.result NOT IN ('A', 'N', 'Z') THEN 1
								ELSE 0
							END
						) AS absences,
						SUM(1) AS total
					FROM	psp_groupstats AS gs, psp_poll AS poll,
							psp_meeting AS meeting
					WHERE	meeting.term_id = %d AND
							poll.meeting_id = meeting.id AND
							gs.group_id = %d AND gs.poll_id = poll.id;
			"""
			select = select % (g.term.id, g.id)
		elif dateStart != None and dateEnd == None:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result = 'A' THEN 1
								ELSE 0
							END
						) AS aye,
						SUM(
							CASE
								WHEN gs.result = 'N' THEN 1
								ELSE 0
							END
						) AS nay,
						SUM(
							CASE
								WHEN gs.result = 'Z' THEN 1
								ELSE 0
							END
						) AS refrains,
						SUM(
							CASE
								WHEN gs.result NOT IN ('A', 'N', 'Z') THEN 1
								ELSE 0
							END
						) AS absences,
						SUM(1) AS total
					FROM	psp_groupstats AS gs, psp_poll AS poll
					WHERE	poll.date = '%d-%02d-%02d' AND
							gs.poll_id = poll.id AND gs.group_id = %d
			"""
			select = select % (	dateStart.year, dateStart.month, dateStart.day,
								groupId
							)
		elif dateStart != None and dateEnd != None:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result = 'A' THEN 1
								ELSE 0
							END
						) AS aye,
						SUM(
							CASE
								WHEN gs.result = 'N' THEN 1
								ELSE 0
							END
						) AS nay,
						SUM(
							CASE
								WHEN gs.result = 'Z' THEN 1
								ELSE 0
							END
						) AS refrains,
						SUM(
							CASE
								WHEN gs.result NOT IN ('A', 'N', 'Z') THEN 1
								ELSE 0
							END
						) AS absences,
						SUM(1) AS total
					FROM	psp_groupstats AS gs, psp_poll AS poll
					WHERE	poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d'
							AND gs.poll_id = poll.id AND gs.group_id = %d
			"""
			select = select % (	dateStart.year, dateStart.month, dateStart.day,
								dateEnd.year, dateEnd.month, dateEnd.day,	
								g.id
							)
		else:
			return None

		cursor = connection.cursor()
		cursor.execute(select)
		rows = cursor.fetchall()

		if len(rows) != 1:
			return None
		
		row = rows[0]
		retVal.votesAye = row[0]
		retVal.votesNay = row[1]
		retVal.votesRefrain = row[2]
		retVal.absences = row[3]
		retVal.totalVotes = row[4]
		retVal.dateStart = dateStart
		retVal.dateEnd = dateEnd

		return retVal

	def getAccordanceWithGroup(self, group, accordGroup, dateStart, dateEnd):
		"""
		Returns copy of group instance extended by attributes:
			dateStart
			dateEnd
			sameResult
			diffResult
			totalPolls
			accordGroup
		dateStart, dateEnd define a day range we are interested in.  if
		dateStart and dateEnd are None then result for complete term is
		returned.  If dateStart only is defined, then result for single
		dateStart day is returned
		"""
		select = ''
		groupId = group_to_id(group)
		accordGroupId = group_to_id(accordGroup)
		retVal = Group.objects.get(id = groupId)

		if dateStart == None and dateEnd == None:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result = accordGs.result THEN 1
								ELSE 0
							END
						) AS same,
						SUM(
							CASE
								WHEN gs.result != accordGs.result THEN 1
								ELSE 0
							END
						) AS diff,
						same + diff AS total
					FROM	psp_groupstats AS gs, psp_groupstats AS accordGs,
							psp_meeting AS m, psp_poll AS poll
					WHERE	m.term_id = %d AND poll.meeting_id = m.id AND
							gs.group_id = %d AND accordGs.group_id = %d AND
							gs.poll_id = poll.id AND accordGs.poll_id = poll.id;
			"""
			select = select % (retVal.term.id, groupId, accordGroupId)
		elif dateStart != None and dateEnd == None:
			seleect = """
				SELECT	SUM(
							CASE
								WHEN gs.result = accordGs.result THEN 1
								ELSE 0
							END
						) AS same,
						SUM(
							CASE
								WHEN gs.result != accordGs.result THEN 1
								ELSE 0
							END
						) AS diff,
						same + diff AS total
					FROM	psp_groupstats AS gs, psp_groupstats AS accordGs,
							psp_poll AS poll
					WHERE	poll.date = '%d-%02d-%02d' AND
							gs.group_id = %d AND accordGs.group_id = %d AND
							gs.poll_id = poll.id AND accordGs.poll_id = poll.id;
			"""
			select = select % (	dateStart.year, dateStart.month, dateStart.day,
								groupId, accordGroupId
							)
		elif dateStart != None and dateEnd != None:
			select = """
				SELECT	SUM(
							CASE
								WHEN gs.result = accordGs.result THEN 1
								ELSE 0
							END
						) AS same,
						SUM(
							CASE
								WHEN gs.result != accordGs.result THEN 1
								ELSE 0
							END
						) AS diff,
						SUM(1) AS total
					FROM	psp_groupstats AS gs, psp_groupstats AS accordGs,
							psp_poll AS poll
					WHERE	poll.date BETWEEN '%d-%02d-%02d' AND '%d-%02d-%02d' AND
							gs.group_id = %d AND accordGs.group_id = %d AND
							gs.poll_id = poll.id AND accordGs.poll_id = poll.id;
			"""
			select = select % (	dateStart.year, dateStart.month, dateStart.day,
								dateEnd.year, dateEnd.month, dateEnd.day,
								groupId, accordGroupId
							)
		else:
			return None

		cursor = connection.cursor()
		cursor.execute(select)
		rows = cursor.fetchall()

		if len(rows) != 1:
			return None
		
		row = rows[0]
		retVal.same = row[0]
		retVal.diff = row[1]
		retVal.total = row[2]
		retVal.accordanceGroup = Group.objects.get(id = accordGroupId)
		retVal.dateStart = dateStart
		retVal.dateEnd = dateEnd

		return retVal

	def createStats(self, poll):
		pollId = poll_to_id(poll)
		insert = """
			INSERT OR IGNORE INTO psp_groupStats (poll_id, group_id, aye, nay, refrains, absences, result)
			SELECT	
					poll.id,
					membership.group_id,
					SUM(
						CASE
							WHEN vote.result = 'A' THEN 1
							ELSE 0
						END
					) AS aye,
					SUM(
						CASE
							WHEN vote.result = 'N' THEN 1
							ELSE 0
						END
					) AS nay,
					SUM(
						CASE
							WHEN vote.result = 'Z' THEN 1
							ELSE 0
						END
					) AS refrains,
					SUM(
						CASE
							WHEN vote.result = '0' OR vote.result = 'X' OR vote.result = 'M' THEN 1
							ELSE 0
						END
					) AS absences,
					' ' as result
				FROM psp_poll AS poll
					INNER JOIN psp_mpVote AS vote ON poll.id = vote.poll_id
					INNER JOIN psp_membership AS membership ON membership.person_id = vote.person_id
					INNER JOIN psp_group AS groupa ON membership.group_id = groupa.id
					INNER JOIN psp_meeting AS meeting ON meeting.term_id = groupa.term_id AND poll.meeting_id = meeting.id
				WHERE (membership.post = 'člen' OR membership.post = 'poslanec') AND poll.id = %d
				GROUP BY poll.id, membership.group_id
			;
		"""
		insert = insert % pollId
		cursor = connection.cursor()
		cursor.execute(insert)
		return


class GroupStats(models.Model):
	"""
	GroupStats represents result (verdict) of a particular group for
	given poll.
	"""
	poll = models.ForeignKey(Poll, db_index = True)
	group = models.ForeignKey(Group, db_index = True)
	votesAye = models.IntegerField(_("Group Aye"), db_column = 'aye')
	votesNay = models.IntegerField(_("Group Nay"), db_column = 'nay')
	votesRefrain = models.IntegerField(	_("Group Refrains"),
										db_column = 'refrains'
									)
	absences = models.IntegerField(_("Group Absences"))
	result = models.CharField(_("Result"), max_length = 20)

	objects = GroupStatsManager()

	class Meta:
		verbose_name = _("Group Statistic")
		verbose_name_plural = _("Group Statistics")
		unique_together = (('poll', 'group',),)

	class Admin:
		pass

	def getResult(self):
		"""
		If result for given poll and group has not been determined yet
		(self.result == ' ' or self.result == '') it must computed.

		if GroupStats was not computed at all 
		(self.nay == 0 and self.aye == 0 ...),
		then it is computed (this is very expensive operation, we are
		trying to avoid).

		Once record is completed it is saved.

		If record is complete (result != ' ' and result != '', the self.result
		value is just returned.

		The result codes are same as for MpVote.result
			A	for
			N	against
			Z	refrain
			0	absent (no difference is being made between excused and absent
				in case of GroupStats)
		"""
		if self.result == ' ' or self.result == '':
			max = {}
			max['value'] = 0
			max['key'] = ''
			for k in (	'votesAye',
						'votesNay',
						'votesRefrain',
						'absences'
					):
				if max['value'] < self.__getattribute__(k):
					max['value'] = self.__getattribute__(k)
					max['key'] = k
				
			if max['key'] == 'votesAye':
				self.result = 'A'
			elif max['key'] == 'votesNay':
				self.result = 'N'
			elif max['key'] == 'votesRefrain':
				self.result = 'Z'
			else:
				self.result = '0'

			self.save()

		if self.result == 'A':
			return _(u'Pro')
		elif self.result == 'N':
			return _(u'Proti')
		elif self.result == 'Z':
			return _(u'Zdrželi')
		else:
			return _(u'Chyběli')


	def getResultChart(self):
		"""
		Returns a pie chart for a group stat. Pie chart shows percent of votes
			for,
			against,
			reefrains,
			absences
		the default size is 400x80
		"""
		total = self.votesAye + self.votesNay + self.votesRefrain + self.absences
		total = total * 1.0
		
		retVal = None
	
		if total != 0:
			retVal = Pie3D([	self.votesAye,
								self.votesNay,
								self.votesRefrain,
								self.absences
							])
			retVal.color(	'007700',
							'770000',
							'007777',
							'777700'
					)
			retVal.label(	_(u'Pro') + ' %d (%.2f%%)' % (	self.votesAye,
															get_percents(
																self.votesAye,
																total
															),
														),
							_(u'Proti') + ' %d (%.2f%%)' % (	self.votesNay,
																get_percents(
																	self.votesNay,
																	total
																),
															),
							_(u'Zdrželo') + ' %d (%.2f%%)' % (	self.votesRefrain,
																get_percents(
																	self.votesRefrain,
																	total
																),
															),
							_(u'Chybělo') + ' %d (%.2f%%)' % (	self.absences,
																get_percents(
																	self.absences,
																	total
																)
															)
						)
			retVal.size(400, 80)

		return retVal
